﻿<cfsilent>
	<cfscript>
		
		markAdvice = getProperty("serviceFactory").getBean("markAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		subjectId = event.getArg('SubID');
		grade = event.getArg('Grade');
		
		sql = "SELECT sbj_name FROM t_subject WHERE sbj_id = :subjectId ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		
		rs_subjectEntity = queryObj.execute( sql=sql ).getResult();
		
		
		/* 学年列表 */
		sql = "select 
					t_term.academic_year, count(*) countStu 
				from   
					t_student_mark a  
					INNER JOIN t_term ON t_term.term_id = a.term_id  
					INNER JOIN t_student ON t_student.stu_id = a.stu_id 
					INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
				where 
					t_class.sbj_id = :subjectId 
					and 
					t_class.grade = :grade 
				group by 
					t_term.academic_year
				order by 
					t_term.academic_year ";
					
		queryObj = new Query( datasource=application.dnsSlave) ;
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		
		rs_termYear = queryObj.execute( sql=sql ).getResult();
		
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_subjectEntity.sbj_name & "专业," & grade & "级-学生成绩明细", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");

		spreadsheetObj = excel.SpreadSheetNew( "学年成绩统计", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学年,成绩数量");
		
		
		for ( i=1; i LTE rs_termYear.recordCount; i++ ){
			
			/* 写入学生信息 */
			rowData = rs_termYear["academic_year"][i] & "," & rs_termYear["countStu"][i];
			
			excel.SpreadsheetAddrow( spreadsheetObj, rowData );
			
		}
		

		for ( i=1; i LTE rs_termYear.recordCount; i++ ){
			
			/* 按学年建立工作簿记录班级学生成绩 */
			excel.SpreadsheetCreateSheet( spreadsheetObj, rs_termYear["academic_year"][i]& "学年" );
			
			excel.SpreadsheetSetActiveSheet( spreadsheetObj, rs_termYear["academic_year"][i] & "学年" );
			
			/* 读取该学年学生成绩 */
			sql = "SELECT 
					  t_term.academic_term, 
					  t_course.course_name, t_course.course_credit, 
					  t_student.stu_id, t_student.stu_name, 
					  t_class.cls_name,
					  t_course_prop.prop_name, t_student_mark.retaking,
					  t_course.mark_mode, t_student_mark.mark_result,
					  t_student_mark.mark_note, t_student_mark.mark_resit,
					  t_student_mark.mark_renote, t_student_mark.mark_resit_limit 
				  FROM 
					  t_student_mark 
					  INNER JOIN t_term ON t_term.term_id = t_student_mark.term_id 
					  INNER JOIN t_course ON t_course.cid = t_student_mark.cid 
					  INNER JOIN t_course_prop ON t_course_prop.prop_id = t_student_mark.prop_id  
					  INNER JOIN t_student ON t_student.stu_id = t_student_mark.stu_id 
					  INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
				  WHERE 
					  t_class.sbj_id = :subjectId 
					  AND 
					  t_class.grade = :grade 
					  AND
					  t_term.academic_year = :termYear 
				  ORDER BY 
					  t_term.academic_term, t_course.course_name, t_student.stu_id ";
				
			queryObj = new Query( datasource=application.dnsSlave ) ;
			queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="termYear", value=rs_termYear["academic_year"][i], cfsqltype="cf_sql_char" );
			queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
			
			rs_mark = queryObj.execute( sql=sql ).getResult();	
		
			/* 添加表头 */
			excel.SpreadsheetAddrow(spreadsheetObj, "学期,课程,学分,学号,姓名,班级,课程性质,重修,正考,补考");

			for ( j=1; j LTE rs_mark.recordCount; j++){
				
				rowData = dictAdvice.getTerm( rs_mark["academic_term"][j]  ) 
						& "," 
						& rs_mark["course_name"][j]
						& "," 
						& numberFormat( rs_mark["course_credit"][j], "_.__" )
						& "," 
						& rs_mark["stu_id"][j]
						& "," 
						& rs_mark["stu_name"][j]
						& "," 
						& rs_mark["cls_name"][j]
						& "," 
						& rs_mark["prop_name"][j];
				
				if ( rs_mark["retaking"][j] eq "1" ) {
					rowData = rowData & ",是,"; 
				}
				else {
					rowData = rowData & ", ,"; 
				}
				
				finalResult = markAdvice.getMark( rs_mark["mark_mode"][j], rs_mark["mark_result"][j], rs_mark["mark_note"][j] );
				resitResult = markAdvice.getMarkResit( rs_mark["mark_mode"][j], rs_mark["mark_resit"][j], rs_mark["mark_renote"][j], rs_mark["mark_resit_limit"][j] );
				
				if ( len(finalResult) ) {
					rowData = rowData & finalResult & ",";
				}
				else {
					rowData = rowData & " ,";
				}

				if ( len(resitResult) ) {
					rowData = rowData & resitResult & ",";
				}
				else {
					rowData = rowData & " ,";
				}
				
				/* 写入成绩信息 */
				excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				
			}

		}

		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>